{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Time Series Data In Pandas\n",
    "\n",
    "*2017-02-03, Josh Montague*\n",
    "\n",
    "This is Part 1 of the \"Time Series Modeling in Python\" series. In this session, we spend time working through creation and manipulation of time series data in the pandas data structures. We'll get a sense for the patterns of use, and try out a handful of the handy built-in methods on dataframes and series that are made specifically for time series data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import random\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Time-like data types\n",
    "\n",
    "To start, we'll look at the specific data types in pandas which facilitate working with time series. They're largely built on top of numpy (as is much of the pandas library, which makes it very fast and efficient), and are designed for interoperability with Python's built-in datetime objects."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## `Timestamp`\n",
    "\n",
    "The Timestamp is similar to Python's `datetime` (and the docs will sometimes interchange the names). But the pandas data structures have a bunch of super useful metadata and methods on them; often, these are precalculated to make subsequent calculations on them more efficient. \n",
    "\n",
    "There's a top-of-the-module constructor that is flexible in parsing the arguments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.Timestamp('2016')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# the parser can do a lot of inference\n",
    "pd.Timestamp('2016 04')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.Timestamp('2016-06-01T11:03')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# this is a handy method for flexibly converting things to time-like things via pandas\n",
    "pd.to_datetime('2017')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.to_datetime('2017-02')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "The "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# tons of helpful attributes and methods\n",
    "ts = pd.Timestamp('2016-06-01 11:03')\n",
    "\n",
    "# filter out the things that aren't intended to be \"user-facing\"\n",
    "[ x for x in dir(ts) if not x.startswith('_') ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# for example, the \"weekday_name\" attribute\n",
    "print(ts.weekday_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## `DatetimeIndex`\n",
    "\n",
    "A DatetimeIndex is a sequence of `Timestamp`s (the flexible interchange of \"datetime\" and \"timestamp\" is slightly confusing), with a type, frequency, and some other attributes.\n",
    "\n",
    "These data types inherit a bunch of super handy things under the hood (like pre-calculated time intervals, and special slicing) which make calculations much faster and easier. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.date_range('2017-02-02', freq='D', periods=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "When dealing with time intervals, there are a lot of magic strings in pandas. I have a general distaste for magic strings, but pandas uses them relatively sparingly.\n",
    "\n",
    "I always get frustrated looking them up, too, so I made [`bit.ly/pd-offsets`](https://bit.ly/pd-offsets) in the hopes of saving a little time trying to remember the correct shorthand for such arcane things as \"semi-month end frequency (15th and end of month)\" and \"business year start frequency\". Fortunately \"weekly\" ('W') and \"daily\" ('D') are sensible."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# weekly data points that fit withinstart and end\n",
    "pd.date_range(start='2017-01', end='2017-02', freq='W')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# specific set of (irregular) epoch seconds\n",
    "pd.to_datetime([1486074696, 1486074698, 1486074699], unit='s')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# the datetimeindex is also a handy iterable (for the array values)\n",
    "ts = pd.date_range(start='2017-01', end='2017-02', freq='W')\n",
    "\n",
    "for t in ts:\n",
    "    print(t, random.choice(['hi','hey','ohai','cheerio']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Timedelta\n",
    "\n",
    "These are *differences* in timestamps. Very useful for math, and with a similarly flexible constructor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.Timedelta('3D')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.Timedelta('-2 days')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.Timedelta(weeks=1, days=2, minutes=7, seconds=3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.Timestamp('2017-02-02 16:00') + pd.Timedelta('-5 hours')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Creating a `DatetimeIndex` on a Dataframe\n",
    "\n",
    "Much of the original motivation for writing pandas was for financial time series modeling. As a result, there are many efficiently implemented methods and conveniences built in the data structures. \n",
    "\n",
    "Many of these depend on dataframe rows being addressed (indexed) with a time-like data type, which is exactly what the DatetimeIndex is for. \n",
    "\n",
    "If our data has some sort of timestamp in it, how do we convert it to an associated datetimeindex?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "d = pd.read_csv('misc/small.csv', names=['date','value'])\n",
    "\n",
    "d.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# is 'date' a data-like data type?\n",
    "d.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "pandas `object` dtype means that at least one of the items in the column was a string. There is no such thing as a `str` type in a pandas dtype.\n",
    "\n",
    "Let's set the 'date' column to be the index first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# by default, also drops the specified column from the frame\n",
    "d.set_index('date').tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# what is the index type? \n",
    "d.set_index('date').index[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Ok, so that approach didn't work. Another way to consider it is to assume (correctly), that if the data is already date-like when we move it to the index, pandas will make a datetimeindex.\n",
    "\n",
    "You've probably seen (and used) this explicit over-writing step to convert a column to a different type. We can use the nice convenience method we saw earlier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# overwrite the column\n",
    "d['date'] = pd.to_datetime(d['date'])\n",
    "\n",
    "d.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# now we have times\n",
    "d.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# setting a time-like type column to the index automatically converts it to a datetimeindex\n",
    "d.set_index('date').index[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Another approach to getting this kind of index from the original file is to parse the dates on file-read."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# we know the dates are in column 0 \n",
    "d2 = pd.read_csv('misc/small.csv', names=['date','value'], parse_dates=[0])\n",
    "\n",
    "d2.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "d2.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "d2.set_index('date').tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "And, there are probably other ways, to be totally honest. Those are the ones that I use most often."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Accessing rows by DatetimeIndex\n",
    "\n",
    "First, note that we can parse the dates and move them right over to the DatetimeIndex all in one compact step with some chainging."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# NB: method chaining can be a nice way to make a handful of sequential manipulations readable\n",
    "d = (pd.read_csv('misc/small.csv', names=['date','value'], parse_dates=[0])\n",
    "     .set_index('date')\n",
    "    )\n",
    "\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "We're familiar with the convenience method for accessing elements of a dataframe: `[]` is shorthand for `__getitem__`, and grabs columns of the frame (returning a series, or another frame if the argument is an array)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# default slicing => *columns*\n",
    "d['value'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "type(d['value'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# what about this?\n",
    "d['2017'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# !!!!!!!\n",
    "\n",
    "This behavior is a special corner of the pandas world, specifically those dataframes that have a datetimeindex. It's a \"fallback\" functionality of the slicing via square brackets. And it is a terrible! If you happen to have columns and index values that are the same, that's too ambiguous.\n",
    "\n",
    "Instead, **when addressing (accessing) a dataframe by row, always use `.loc` (name) or `.iloc` (integer)**. \n",
    "\n",
    "Think: `.loc`, like \"location; `.iloc` like \"integer\\* location\".\n",
    "\n",
    "Another option is `.ix`, which accessing rows by either names or position (integer), but honestly, why risk the ambiguity? Just choose an explicit one and the let it `KeyError` if you choose wrong. Eventually you will have a row indexed by the *string* '0' and you will spend 15 minutes debugging this, and then you will be sad.\n",
    "\n",
    "\\*(or \"index location\", but the term index is pretty overloaded in this context) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# location\n",
    "d.loc['2017-01-02']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# integer location\n",
    "d.iloc[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "Note the difference in precision between the argument in `.loc['2017-01-02']` and that shown in the 'Name:' (and remember that numpy's datetimes have nanosecond precision, under the hood)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# the timestamp repr displays down to the minute\n",
    "d.index[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "This is enabled by partial string indexing - another awesome feature of the datetimeindex. You can think of it as automatically expanding to encompass \"all of the index values that would match as much of a criteria as you've specified.\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "d.loc['2017-02'].tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Transformations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Shifting\n",
    "\n",
    "This isn't really a time series-related transformation, but it's useful so why not mention it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "d.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# shift (either + or -)\n",
    "d.shift(2).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Resampling\n",
    "\n",
    "This is where the power of the datetimeindex really starts to show. These calculations are fast, even on very large dataframes.\n",
    "\n",
    "Resampling takes advantage of the same split-apply-combine methodology as groupby calculations. This is one of the places where pandas can run many calculations at the same time for a big speed-up (RELEASE THE KRAKE... er, GIL)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "d.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# kind of like a groupby object - needs an 'and then what?'\n",
    "d.resample('7d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "(d.resample('7d')\n",
    " .sum()\n",
    " .head()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# convenient for plotting\n",
    "for freq in ('1d', '3d', '1w'):\n",
    "    plt.plot( (d.resample(freq)\n",
    "               # choose one!\n",
    "               .sum()\n",
    "               #.mean()               \n",
    "               #.min()               \n",
    "              )\n",
    "               , 'o--', label=freq)\n",
    "\n",
    "plt.legend()\n",
    "fig = plt.gcf(); fig.autofmt_xdate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "You can also resample in the other direction (upsampling), where you don't have all the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# daily data\n",
    "d.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# \"resample\" to 6-hourly data (4x more observations)\n",
    "(d.resample('6h')\n",
    " .sum()\n",
    " .head(20)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "That's not immediately useful because all we did was add a bunch of NaNs. But that's ok, because we can prescribe how the frame should fill in those observations..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# options for filling the NaNs - choose one!\n",
    "(d.resample('6h')\n",
    "\n",
    " # forward fill\n",
    " #.ffill()\n",
    " \n",
    " # backward fill\n",
    " #.bfill()\n",
    " \n",
    " # interpolate\n",
    " .interpolate('linear')\n",
    " \n",
    " # arb. (asfreq() => df)\n",
    " #.asfreq().fillna(-1)\n",
    ").head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "And when we chart it, we can see a little more clearly how e.g. the liner interpolation works."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "rs = d.resample('6h').interpolate('linear')\n",
    "\n",
    "plt.figure(figsize=(10,6))\n",
    "\n",
    "# NB: mpl 2.0 takes dataframes as arguments!\n",
    "plt.plot(rs['2017-02'], 'o--', label='interpolated')\n",
    "plt.plot(d['2017-02'], 'o', markersize=20, markerfacecolor='none', label='original')\n",
    "\n",
    "fig = plt.gcf(); fig.autofmt_xdate()\n",
    "plt.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# References\n",
    "\n",
    "Here are some of the useful tabs I had open while working on this session.\n",
    "\n",
    "- pandas docs on [time series functionality](http://pandas.pydata.org/pandas-docs/stable/timeseries.html#)\n",
    "- [the time series installation](Tom Augspurger's ) of Tom Augspurger's \"Modern Pandas\" (*this entire series is A+*)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
